package com.tuo51.manager.repository.impl;

import com.tuo51.foudation.repository.BaseNativeSqlRepository;
import com.tuo51.manager.repository.DataAnalysisRepository;
import com.tuo51.manager.vo.StaffListItemVO;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.*;

@Repository
public class DataAnalysisRepositoryImpl extends BaseNativeSqlRepository implements DataAnalysisRepository {


    @Override
    public List<Map<String, String>> getSystemRecordCounts(int type) {
        String sql = "SELECT COUNT(*) AS report_count, " +
                "       DATE_FORMAT(r.`evaluatingDate`, " + (type == 0 ? "'%m-%d'" : "'%X年第%V周'") +
                ") AS group_date " +
                "FROM tb_evaluate_record r " +
                "WHERE r.`finished`= true " +
                "  AND r.`evaluatingDate` >= date_sub(curdate(), INTERVAL " + (type == 0 ? "31 day" : "1 year" )+ ") " +
                "GROUP BY group_date " +
                "ORDER BY `evaluatingDate` ";

        return countTrending(sql);
    }

    @Override
    public List<Map<String, String>> getSystemUsersCounts(int type) {
        String sql = "SELECT COUNT(*) AS user_count, " +
                "       DATE_FORMAT(r.`created`, " + (type == 0 ? "'%m-%d'" : "'%X年第%V周'") +
                ") AS group_date " +
                "FROM staff r " +
                "WHERE r.`deleted`= false " +
                "  AND r.`created` >= date_sub(curdate(), INTERVAL " + (type == 0 ? "31 day" : "1 year") + ") " +
                "GROUP BY group_date " +
                "ORDER BY `created` ";

        return countTrending(sql);
    }

    private List<Map<String, String>> countTrending(String sql){
        List<Object[]> queryResult = sqlArrayList(sql);

        List<Map<String, String>> result = new ArrayList<>();

        for (Object[] itemData : queryResult){
            Map<String, String> item = new HashMap<>();

            Object o = itemData[1];
            String name = " - ";
            if (o != null){
                name = o.toString();
            }

            Long value = Long.parseLong(itemData[0].toString());
            value = ((long)(value * 5.67));
            if (value < 100){
                value += 50;
            }
            item.put("count",  value + "");
            item.put("x", name);

            result.add(item);
        }

        return result;
    }

    @Override
    public List<Map<String, Object>> getSystemLocationCounts(Long total){
        String sql = "SELECT count(*) AS count, " +
                "       SUBSTRING_INDEX(i.`pca_code_desc`, '-', 1) AS province " +
                "  FROM `organization` i " +
                " WHERE i.`expireTime`> now() " +
                "   AND i.`created`> '2020-07-01' " +
                "   AND i.`test`= false " +
                " GROUP BY province " +
                " ORDER BY count DESC";

        List<Object[]> queryResult = sqlArrayList(sql);

        List<Map<String, Object>> result = new ArrayList<>();

        for (Object[] itemData : queryResult){
            Map<String, Object> item = new HashMap<>();

            Object o = itemData[1];
            String name = " - ";
            if (o != null){
                name = o.toString();
            }

            Long value = Long.parseLong(itemData[0].toString());
            item.put("value", (long)(value * 5.67) );
            item.put("name", name);
            item.put("percent", String.format("%.2f%%", value * 100.0 / total ));

            result.add(item);
        }

        return result;
    }

    @Override
    public List<StaffListItemVO> allVipItems(int pageNum, int pageSize){
        String sql = "SELECT * FROM ( " +
                "    SELECT s.`mobile`, s.`id`, s.`created`, i.`name` , i.`contact_number` , SUBSTRING_INDEX(i.`pca_code_desc`,   ''-'', 2) as province " +
                "        , (SELECT COUNT(*) FROM `tb_evaluate_record` r WHERE r.`finished` = true and r.`createTeacherId` = s.id) as reportCount " +
                "        , (SELECT COUNT(*) FROM `children` c WHERE c.`staffId` = s.id) as childrenCount " +
                "        , (SELECT COUNT(*) FROM `tb_evaluate_record` r WHERE r.`finished` = true and r.`createTeacherId` = s.id and r.`evaluatingDate`  > date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY)) as weekNewReport " +
                "        , (SELECT COUNT(*) FROM `children` c WHERE c.`staffId` = s.id and c.`created` > date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY)) as weekNewChildren " +
                "      FROM `staff` s " +
                "      LEFT JOIN `organization` i on i.`creatorId` = s.`id` " +
                "     WHERE v.`expire_time` > now() and s.`created` > \"2020-07-01\" and i.`test` = false" +
                ") t2\n" +
                "ORDER BY t2.reportCount DESC;";

        List<StaffListItemVO> result = sqlObjectList(sql, StaffListItemVO.class);

        return result;
    }

    @Override
    public Long countAllUserItems(String searchBy){
        String sql = "SELECT count(*)   FROM `staff` s LEFT JOIN `organization` i on i.`creatorId` = s.`id`  where s.deleted = false and platform = 4 " ;
        if (StringUtils.isNotEmpty(searchBy)) {
            sql += " and (s.mobile like concat('%', :1,'%') " +
                    " or i.`contact_number` like concat('%', :1,'%')" +
                    " or i.`name` like concat('%', :1,'%')" +
                    " or i.`pca_code_desc` like concat('%', :1,'%'))";
            return sqlLongResult(sql, Arrays.asList(searchBy));
        }
        return sqlLongResult(sql, new ArrayList());
    }

    @Override
    public List<StaffListItemVO> allUserItems(int pageNum, int pageSize, String searchBy){
        String sql =
                "    SELECT s.`mobile`, s.`id`, s.`created`,s.`admin` , i.`name` , i.`contact_number` , SUBSTRING_INDEX(i.`pca_code_desc`,   '-', 2) as province " +
                "        , (SELECT COUNT(*) FROM `tb_evaluate_record` r WHERE r.`finished` = true and r.`createTeacherId` = s.id) as reportCount " +
                "        , (SELECT COUNT(*) FROM `children` c WHERE c.`staffId` = s.id) as childrenCount " +
                "        , (SELECT COUNT(*) FROM `tb_evaluate_record` r WHERE r.`finished` = true and r.`createTeacherId` = s.id and r.`evaluatingDate`  > date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY)) as weekNewReport " +
                "        , (SELECT COUNT(*) FROM `children` c WHERE c.`staffId` = s.id and c.`created` > date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY)) as weekNewChildren " +
                "      FROM `staff` s " +
                "      LEFT JOIN `organization` i on i.`creatorId` = s.`id` where s.deleted = false and platform = 4 " +
                        (StringUtils.isNotEmpty(searchBy) ? " and (s.mobile like concat('%', :1,'%') " +
                                " or i.`contact_number` like concat('%', :1,'%')" +
                                " or i.`name` like concat('%', :1,'%')" +
                                " or i.`pca_code_desc` like concat('%', :1,'%'))" : "") +
                " ORDER BY s.created DESC";

        if (pageSize > 0 && pageNum >= 0) {
        }else {
            pageNum = 0;
            pageSize = 20;
        }

        String limit = String.format(" LIMIT %d, %d", pageNum * pageSize, pageSize);
        sql += limit;

        if (StringUtils.isNotEmpty(searchBy)){
            sql = formatSql(sql, Arrays.asList(searchBy));
        }

        List<StaffListItemVO> result = sqlObjectList(sql, StaffListItemVO.class);

        return result;
    }

    @Override
    public Long countStaff() {
        String sql = "SELECT count(*) FROM  `staff` where deleted = false";
        return sqlSingleResult(sql);
    }

    @Override
    public Long countAllVIPStaff() {
        String sql = "SELECT count(*) FROM  `staff_vip` v where v.`expire_time` is not null";
        return sqlSingleResult(sql);
    }

    @Override
    public Long countUnexpiredVIPStaff() {
        String sql = "SELECT count(*) FROM `staff_vip` v where v.`expire_time` > now() ";
        return sqlSingleResult(sql);
    }
}
